package mappers;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import managers.DatabaseConnection;
import domains.Contingent;
import org.joda.time.DateTime;

public class ContingentMapper {
	public ArrayList<Contingent> selectContingentInfo(int index) {
		ArrayList<Contingent> contingentInfo = new ArrayList<Contingent>();
		String sql = "SELECT * FROM hovedopgave.contingent WHERE member_id = ?";
		try {
			DatabaseConnection dbConnect = new DatabaseConnection();
			PreparedStatement query = dbConnect.getConnect().prepareStatement(
					sql);
			query.setInt(1, index);
			ResultSet rs = query.executeQuery();

			while (rs.next()) {
				DateTime dt = new DateTime(rs.getTimestamp(3).getTime());
				DateTime dt1 = new DateTime(rs.getTimestamp(4).getTime());
				Contingent c = new Contingent(rs.getInt("contingent_id"), dt,
						dt1, rs.getInt("member_id"));

				contingentInfo.add(c);
			}

			rs.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return contingentInfo;
	}

	public void addContingent(Contingent c) {
		String sql = "insert into hovedopgave.contingent (member_id,"
				+ " contingent_start_date, contingent_end_date) values (?, ?, ?)";
		try {
			DatabaseConnection dbConnect = new DatabaseConnection();
			PreparedStatement query = dbConnect.getConnect().prepareStatement(
					sql);

			query.setInt(1, c.getMemberId());
			query.setDate(2, new java.sql.Date(c.getStartDate().getMillis()));
			query.setDate(3, new java.sql.Date(c.getEndDate().getMillis()));

			query.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public ArrayList<Contingent> renewContingentInfo(int index) {
		ArrayList<Contingent> contingentInfo = new ArrayList<Contingent>();
		String sql = "SELECT member.member_fname, member.member_lname, "
				+ "contingent.contingent_id, contingent.contingent_start_date, "
				+ "contingent.contingent_end_date FROM hovedopgave.member "
				+ "INNER JOIN hovedopgave.contingent on member.member_id "
				+ "= contingent.member_id WHERE member.member_id = ?";
		try {
			DatabaseConnection dbConnect = new DatabaseConnection();
			PreparedStatement query = dbConnect.getConnect().prepareStatement(
					sql);
			query.setInt(1, index);
			ResultSet rs = query.executeQuery();

			while (rs.next()) {
				DateTime dt = new DateTime(rs.getTimestamp(4).getTime());
				DateTime dt1 = new DateTime(rs.getTimestamp(5).getTime());
				Contingent c = new Contingent(
						rs.getString("member_fname"),
						rs.getString("member_lname"),
						rs.getInt("contingent_id"), 
						dt,
						dt1);

				contingentInfo.add(c);
			}

			rs.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return contingentInfo;
	}
}
